﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using fsw.DBUtility;
using fsw.Common;
using System.Reflection;

namespace fsw.DAL
{
    public class Admin_Project
    {
        private string databaseprefix; //数据库表名前缀
        public Admin_Project(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }
        #region 基本操作方法================
        /// <summary>
        /// 根据id判断是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "Admin_Project");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 添加管理员项目
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="models"></param>
        /// <param name="UserId"></param>
        public void Add(SqlConnection conn, SqlTransaction trans, List<Model.Admin_Project> models, int UserId)
        {
            if (models != null)
            {
                StringBuilder strSql;
                StringBuilder str1; ;//数据字段
                StringBuilder str2;//数据参数
                foreach (Model.Admin_Project modelt in models)
                {
                    strSql = new StringBuilder();
                    str1 = new StringBuilder();
                    str2 = new StringBuilder();
                    //利用反射获得属性的所有公共属性
                    PropertyInfo[] pros = modelt.GetType().GetProperties();
                    List<SqlParameter> paras = new List<SqlParameter>();
                    strSql.Append("insert into " + databaseprefix + "Admin_Project(");
                    foreach (PropertyInfo pi in pros)
                    {
                        //如果不是主键则追加sql字符串
                        if (!pi.Name.Equals("Id"))
                        {
                            //判断属性值是否为空
                            if (pi.GetValue(modelt, null) != null)
                            {
                                str1.Append(pi.Name + ",");//拼接字段
                                str2.Append("@" + pi.Name + ",");//声明参数
                                switch (pi.Name)
                                {
                                    case "UserId":
                                        paras.Add(new SqlParameter("@" + pi.Name, UserId));//刚插入的关键词内空
                                        break;
                                    default:
                                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(modelt, null)));//对参数赋值
                                        break;
                                }
                            }
                        }
                    }
                    strSql.Append(str1.ToString().Trim(','));
                    strSql.Append(") values (");
                    strSql.Append(str2.ToString().Trim(','));
                    strSql.Append(") ");
                    DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                }
            }
        }
        /// <summary>
        /// 更新管理员项目
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="models"></param>
        /// <param name="UserId"></param>
        public void Update(SqlConnection conn, SqlTransaction trans, List<Model.Admin_Project> models, int UserId)
        {
            DeleteList(conn, trans, models, UserId);
            if (models != null)
            {
                StringBuilder strSql;
                StringBuilder str1;//数据字段
                StringBuilder str2;//数据参数
                foreach (Model.Admin_Project modelt in models)
                {
                    strSql = new StringBuilder();
                    str1 = new StringBuilder();
                    str2 = new StringBuilder();
                    //利用反射获得属性的所有公共属性
                    PropertyInfo[] pros = modelt.GetType().GetProperties();
                    List<SqlParameter> paras = new List<SqlParameter>();
                    if (modelt.Id > 0)
                    {
                        strSql.Append("update " + databaseprefix + "Admin_Project set ");
                        foreach (PropertyInfo pi in pros)
                        {
                            //如果不是主键则追加sql字符串
                            if (!pi.Name.Equals("Id"))
                            {
                                //判断属性值是否为空
                                if (pi.GetValue(modelt, null) != null)
                                {
                                    str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                                    paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(modelt, null)));//对参数赋值
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(" where id=@id");
                        paras.Add(new SqlParameter("@id", modelt.Id));
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray());
                    }
                    else
                    {
                        strSql.Append("insert into " + databaseprefix + "Admin_Project(");
                        foreach (PropertyInfo pi in pros)
                        {
                            //如果不是主键则追加sql字符串
                            if (!pi.Name.Equals("Id"))
                            {
                                //判断属性值是否为空
                                if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals(""))
                                {
                                    str1.Append(pi.Name + ",");//拼接字段
                                    str2.Append("@" + pi.Name + ",");//声明参数
                                    paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(modelt, null)));//对参数赋值
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(") values (");
                        strSql.Append(str2.ToString().Trim(','));
                        strSql.Append(") ");
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                    }
                }
            }
        }
        /// <summary>
        /// 删除不存在的数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="models"></param>
        /// <param name="UserId"></param>
        public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Model.Admin_Project> models, int UserId)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Model.Admin_Project modelt in models)
                {
                    if (modelt.Id > 0)
                    {
                        idList.Append(modelt.Id + ",");
                    }
                }
            }
            string delIds = idList.ToString().TrimEnd(',');
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id from " + databaseprefix + "Admin_Project where UserId = " + UserId);
            if (!string.IsNullOrEmpty(delIds))
            {
                strSql.Append(" and id not in(" + delIds + ")");
            }
            DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "Admin_Project where id=" + dr["id"].ToString()); //删除数据库              
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Model.Admin_Project> GetList(int UserId)
        {
            List<Model.Admin_Project> modelList = new List<Model.Admin_Project>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "Admin_Project ");
            strSql.Append(" where UserId=" + UserId);
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                for (int n = 0; n < dt.Rows.Count; n++)
                {
                    modelList.Add(DataRowToModel(dt.Rows[n]));
                }
            }
            return modelList;
        }
        /// <summary>
        /// 将对象转换实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public Model.Admin_Project DataRowToModel(DataRow row)
        {
            Model.Admin_Project model = new Model.Admin_Project();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
            }
            return model;
        }
        #endregion
    }
}
